﻿using QDAS;
using QDasConverter.Utils;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using WindGoes6.Database;

namespace QDasConverter.Core
{
    /// <summary>
    /// 大众SqlServer转换器，2021年4月开始，联系人：沈工
    /// </summary>
    public class T2021R02 : ConvertBase
    {
        public bool DebugMode = true;
        IniAccess ia = new IniAccess("config.ini");
        int lastResultID = 0;
        int batch_count = 10;
        SQLManager sm;
        string[][] results = null;
        Dictionary<string, string[]> kvs;
        string outputDirectory = "C:\\Qdas\\output";
        string tempDirectory = "T2021R02";

        public T2021R02()
        {
            DisplayName = "WVSql转换器";
            Version = "alpha1 Released Date: 2021/05/17 Inner Name: T2021R01_WVSql";
            Version = "alpha2 Released Date: 2021/09/20 Inner Name: T2021R01_WVSql";
            Version = "V1.0.0 Released Date: 2021/09/28 (Inner Code: T2021R01_WVSql)";
            Version = "V1.0.1 Released Date: 2021/09/28 (Inner Code: T2021R01_WVSql)";

            // 添加日志开关功能，只要目录下有 AnyFile.DeBug 文件，就会进入Debug模式
            Version = "V1.0.2 Released Date: 2021/09/28 (Inner Code: T2021R01_WVSql)";

            Extensions = "*.*";
        }

        public override void Initialize()
        {
            base.Initialize();
        }

        public override bool OnStart()
        {
            string cs = ia.ReadString(Config.ConnectionString, true);
            // cs = "Data Source = 117.71.62.132,8092;Initial Catalog = A30;User ID = sa; Password = Huaun@2021;";
            if (string.IsNullOrEmpty(cs))
                return false;

            SQLManager.CommonConnectionString= cs;
            sm = new SQLManager();

            bool res = false;
            try
            {
                res = sm.Open();
                sm.Close();
            }
            catch (Exception ex)
            {
                Common.AddLog($"打开数据库失败，连接字符串：{cs}, 错误原因：{ex.Message}.");
                return false;
            }

            kvs = GetKeyValues();
            batch_count = ia.ReadInt("BatchCount", 20);
            lastResultID = ia.ReadInt("LastResultID", 0);
            tempDirectory = ia.ReadString("TempFolder");
            outputDirectory = ia.ReadString("OutputFolder");

            return res;
        }

        public override void OneLoop_Prepare()
        {
            // 2021/05/16 注意：spc_config中的ID都是2个，所以结果是双倍的。
            sm.CommandText = $"SELECT TOP ({batch_count}) " +
@"
        spc_spcdata.ID AS ResultID,
        stn_Station.StationName AS K1086, 
        spc_spcconfig.Name AS K2002, 
        spc_spcconfig.LowerLimit AS K2110, 
        spc_spcconfig.UpperLimit AS K2111, 
        spc_spcdata.Value AS K0001, 
        spc_spcdata.DateTime AS K0004, 
        spc_spcdata.ProductCode AS K0014
FROM  spc_spcdata INNER JOIN
        spc_spcconfig ON spc_spcdata.SPCID = spc_spcconfig.SPCID LEFT OUTER JOIN
        stn_Station ON spc_spcconfig.StationNr = stn_Station.StationNr
WHERE
        spc_spcdata.ID > " + lastResultID + " ORDER BY spc_spcdata.ID";

            if (DebugMode)
            {
                Common.AddLog("dm.CommandText: " + sm.CommandText);
                Common.AddLog("dm.CurrentConnection: " + SQLManager.CommonConnectionString);
                Common.AddLog("dm.ConnectionString: " + DBManager.ConnectionString);
            }

            CleanTempFolder(tempDirectory);

        }

        public override void OneLoop_Process()
        {
            results = sm.GetStrings();

            if (results?.Length == 0)
            {
                Common.AddLog("No updated yet, ResultID = " + lastResultID);
                return;
            }


            // 开始数据转换
            QFile qf = new QFile();
            foreach (var data in results)
            {
                qf = new QFile();
                qf[1001] = "04E 100 012 ";
                if (!string.IsNullOrEmpty(data[7]) && data[7].Length >= 3)
                    qf[1001] = "04E 100 012 " + data[7].Substring(0, 3); // 还要加上K0014的前3位
                string K1086 = data[1];
                qf[1086] = K1086;
                qf[1091] = "MON4";

                var qc = qf.AddQCharacteristic();
                qc[2002] = data[2];
                qc[2110] = data[3];
                qc[2111] = data[4];
                qc[2022] = 4;
                qc[8500] = 5;
                qc[8501] = 5;
                var item = qc.AddItem();
                item.SetValue(data[5]);
                // K1086映射至 K0010 + K0012
                if (kvs.Keys.Contains(K1086))
                {
                    item[0010] = kvs[K1086][1];
                    item[0012] = kvs[K1086][2];
                }
                item[0014] = data[7];
                item.date = DateTime.Parse(data[6].ToString());

                qf.ToDMode();
                string output_filename = $"{K1086}_{data[0]}_{DateTime.Now:yyyyMMdd_HHmmss.fff}.dfq";
                string output_filepath = Path.Combine(tempDirectory, output_filename);

                if (File.Exists(output_filepath))
                    File.Delete(output_filepath);
                qf.SaveToFile(output_filepath);
            }
        }

        public override void OneLoop_Completed()
        {
            foreach (var source in Directory.GetFiles(tempDirectory))
            {
                string target = Path.Combine(outputDirectory, Path.GetFileName(source));
                File.Copy(source, target);
            }

            ia.WriteValue("LastResultID", (lastResultID + batch_count).ToString());

            LogList.Add(new TransLog()
            {
                LogDateTime = DateTime.Now,
                LogType = LogType.INFO,
                LogContent = $"转换成功 {results.Length}条记录, Start={lastResultID}, End = {lastResultID + results.Length - 1}。",
                Input = "无",
                Remark = "From T2021R01_WVSql"
            });

            Funs.Delay();
        }

        int counter = 0;

        public override bool IsLoopCompleted()
        {
            Console.WriteLine("counter=" + counter);
            return counter++ > 3;
        }

        /// <summary>
        /// 从文件中读取映射信息。映射规则如下：
        /// # 【使用注意事项】
        /// # 1、映射关系包括K1086，K0010和K0012，其中由第1列K1086决定后面两项内容。
        /// # 2、三个映射关系使用逗号分开，逗号请使用半角。
        /// # 3、以#号开头的行和空行被忽略。
        /// # 4、文件必需使用UTF-8编码，否则可能出现乱码。
        /// # 5、如果同一项（第1列）相同，只添加一次。
        /// </summary>
        /// <param name="keyfield_file">输入配置文件，默认名为 keyfields.txt</param>
        /// <returns></returns>
        public Dictionary<string, string[]> GetKeyValues(string keyfield_file = "keyfields.txt")
        {
            Dictionary<string, string[]> dict = new Dictionary<string, string[]>();
            if (!File.Exists(keyfield_file))
                return dict;

            foreach (var line in File.ReadAllLines(keyfield_file, Encoding.UTF8))
            {
                string line1 = line.Trim();
                if (line1.Length == 0 || line1.StartsWith("#") || line1.StartsWith("//"))
                    continue;

                var items = line1.Replace("，", ",").Split(',');
                if (items.Length != 3)
                    continue;

                if (!dict.Keys.Contains(items[0]))
                    dict.Add(items[0], items);
            }

            return dict;
        }


        /// <summary>
        /// 2020/11/02添加，使用正则对名称进行匹配。
        /// </summary>
        /// <param name="qf">待保存的QFile对象。</param>
        /// <param name="outpath">输出路径，格式可能为 c:\qdas\output\data\202011\tx032_[1001]_20201102_0729.dfq</param>
        /// <returns></returns>
        public bool SaveDfq(QFile qf, string outpath)
        {
            Regex reg = new Regex(@"\[(\d{1,4})\]");
            string filename = Path.GetFileNameWithoutExtension(outpath);
            foreach (Match match in reg.Matches(filename))
            {
                try
                {
                    int key = int.Parse(match.Groups[1].Value);
                    if (qf[key] != null && qf[key].ToString().Length > 0)
                        filename = filename.Replace('[' + match.Groups[1].Value + ']', qf[key].ToString());
                }
                catch { }
            }

            // 路径中不能包括
            if (!string.IsNullOrEmpty(filename))
            {
                foreach (char ch in "\\/:*?\"<>.|")
                {
                    filename = filename.Replace(ch, '_'); ;
                }
            }

            return qf.SaveToFile(Path.Combine(Path.GetDirectoryName(outpath), filename + ".dfq"));
        }

    }
}

/**

        /// <summary>
        /// 核心转换函数。
        /// </summary>
        /// <param name="inpath">输入文件路径。</param>
        /// <param name="outpath">输出文件路径。</param>
        /// <returns></returns>
        public bool ConvertExcel(string inpath, string outpath)
        {
            // 读取不到数据返回false
            var ws = Common.LoadWorkSheet(inpath, 0);
            if (ws == null)
                return false;

            // 读取到的数据长度为0，返回false
            var data = Common.ToStringList(ws);
            if (data.Count == 0)
                return false;


            QFile qf = new QFile();

            string K1001 = "", K1002 = "", Kdate = "", Ktime = "";
            int rowid = 1;
            for (; rowid < data.Count; rowid++)
            {
                if (data[rowid][1].Contains("Part Name"))
                    K1002 = data[rowid][2];
                else if (data[rowid][1].Contains("Date"))
                    Kdate = data[rowid][2];
                else if (data[rowid][1].Contains("Time"))
                    Ktime = data[rowid][2];
                else if (data[rowid][1].Trim().Length == 0)
                    break;
            }

            for (int i = 1; i < data.Count - 1; i++)
            {
                if (data[i - 1][1].Trim().Length == 0 && data[i][1].Trim().Length > 0 && data[i + 1][1].Trim().Length == 0)
                {
                    K1001 = data[i][1];
                }
            }

            qf[1001] = K1001;
            qf[1002] = K1002;
            qf[1203] = "Standard Production";
            qf[1900] = "PC - DMIS - Q - DAS Converter Version  5.2015.109";


            DateTime date = DateTime.Parse(Kdate + " " + Ktime);

            for (; rowid < data.Count; rowid++)
            {
                var row = data[rowid];
                if (!row[1].Contains("Dimension"))
                    continue;

                List<RowData> list = new List<RowData>();
                // 处理 位置度
                if (list[0].K2001.Contains("位置") && list.Count > 1)
                {
                    foreach (var rd in list)
                    {
                        var ch = qf.AddQCharacteristic();
                        rd.Fill(ch);
                        var di = ch.AddItem();
                        di.value = rd.value;
                        di.date = date;
                    }
                }
                else
                {
                    foreach (var rd in list)
                    {
                        var ch = qf.AddQCharacteristic();
                        rd.Fill(ch);
                        var di = ch.AddItem();
                        di.value = rd.value;
                        di.date = date;
                    }
                }
            }

            qf.ToDMode();

            return SaveDfq(qf, outpath);
        }
        /// <summary>
        /// K1086映射至 K0010 + K0012
        /// </summary>
        /// <param name="config_filename"></param>
        /// <returns></returns>
        public Dictionary<string, string> LoadKeyValues(string config_filename = "config_ks.txt")
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            if (!File.Exists(config_filename))
                return dict;

            foreach (var item in File.ReadAllLines(config_filename))
            {
                if (string.IsNullOrEmpty(item) || item.StartsWith("#") || item.Trim().Length == 0)
                    continue;

                var items = item.Split(',');
                if (items.Length == 3)
                {
                    if (!dict.Keys.Contains(items[0]))
                    {
                        dict.Add(items[0], items[1] + ',' + items[2]);
                    }
                }
            }

            return dict;
        }  
 */